# Copyright (c) 2022, hzmes and contributors
# For license information, please see license.txt

# from turtle import Turtle
from calendar import month
import frappe
from frappe.utils import get_first_day, get_last_day, nowdate, add_to_date


# 当天
current_day = nowdate()
# 去年当天
prev_year_day = add_to_date(current_day, years = -1)
# 当月第一天
first_day = get_first_day(current_day)
# 当月最后一天
last_day = get_last_day(current_day)
# 上月当天
prev_day = add_to_date(current_day, months = -1)
# 上月第一天
prev_first_day = get_first_day(prev_day)
# 上月最后一天
prev_last_day = get_last_day(prev_day)


def execute(filters=None):
	# get_data要传参filters
	return get_columns(), get_data(filters), None, get_chart(), get_report_summary()

def get_columns():
	return ["Tax Identify:Link/SRM Supplier:280", "Supplier Number:180", "Supplier Name:180", "Company Nature:50", "Full Name:50", "Email Address:100", "Category Item:50"]

def get_data(filters):
	# 获取filters
	# frappe.msgprint(f'the filter is {filters}')
	_is_have_user, _company_nature = filters.get('is_have_user'), filters.get('company_nature')
	# 先配置默认conditions再通过if判断是否增加条件项
	conditions = " AND 1=1 "
	if _is_have_user:
		conditions += f" AND s1.is_have_user = '{_is_have_user}' "
	if _company_nature:
		conditions += f" AND s1.company_nature = '{_company_nature}' "
	# 把conditions组合进来
	dt = frappe.db.sql(
		f"""SELECT s1.tax_identify, s1.supplier_number, s1.supplier_name, s1.company_nature, s2.full_name, s2.email_address, s3.category_item 
		FROM `tabSRM Supplier` s1 
		LEFT JOIN `tabSRM Supplier Contact Sub` s2 on s1.name = s2.parent 
		LEFT JOIN `tabSRM SupplierCategory Sub` s3 on s1.name = s3.parent
		WHERE s1.to_formal = 0 {conditions};
		""", as_dict=True
	)
	return dt

def get_chart():
	cur_month_total_count = frappe.db.sql(f"""SELECT count(s1.name) FROM `tabSRM Supplier` s1 WHERE s1.creation BETWEEN '{first_day}' AND '{last_day}';""")
	prev_month_total_count = frappe.db.sql(f"""SELECT count(s1.name) FROM `tabSRM Supplier` s1 WHERE s1.creation BETWEEN '{prev_first_day}' AND '{prev_last_day}';""")
	prev_year_total_count = frappe.db.sql(f"""SELECT count(s1.name) FROM `tabSRM Supplier` s1 WHERE s1.creation BETWEEN '{prev_year_day}' AND '{current_day}';""")
	cur_month_convert_count = frappe.db.sql(f"""SELECT count(s1.name) FROM `tabSRM Supplier` s1 WHERE s1.convert_date BETWEEN '{first_day}' AND '{last_day}';""")
	prev_month_convert_count = frappe.db.sql(f"""SELECT count(s1.name) FROM `tabSRM Supplier` s1 WHERE s1.convert_date BETWEEN '{prev_first_day}' AND '{prev_last_day}';""")
	prev_year_convert_count = frappe.db.sql(f"""SELECT count(s1.name) FROM `tabSRM Supplier` s1 WHERE s1.convert_date BETWEEN '{prev_year_day}' AND '{current_day}';""")	
	chart = {
		'data':{
			'labels':['当月','上月','最近12个月'],
			'datasets':[
				{'name':'注册','values':[cur_month_total_count, prev_month_total_count, prev_year_total_count]},
				{'name':'转化','values':[cur_month_convert_count, prev_month_convert_count, prev_year_convert_count]}
			]
		},
		'type':'bar'
	}
	return chart

def get_report_summary():
	total_count = frappe.db.sql(f"""SELECT count(s1.name) FROM `tabSRM Supplier` s1;""")
	convert_count = frappe.db.sql(f"""SELECT count(s1.name) FROM `tabSRM Supplier` s1 WHERE s1.to_formal =1;""")
	if total_count and total_count[0][0] > 0:
		report_summary = [
			{"label":"总注册数量","value": total_count[0][0], 'indicator':'Red'},
			{"label":"转化数量","value": convert_count[0][0], 'indicator':'Blue'},
			{"label":"转化率(%)","value": round(convert_count[0][0] / total_count[0][0] * 100, 0), 'indicator': 'Yellow'}
		]
	else:
		report_summary = [
			{"label":"总注册数量","value":0,'indicator':'Red'},
			{"label":"转化数量","value":0,'indicator':'Blue'},
			{"label":"转化率(%)","value":0,'indicator':'Yellow'}
		]
	return report_summary